﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using JDML.StaticExtensions;
using JDML.BindingList;

namespace JDML.MappingClass
{
    public class SQLMapping : BaseMapping
    {
        public Dictionary<string, SQLCmdProperty> SelectSQLCommands { get; set; }
        public Dictionary<string, SQLCmdProperty> InsertSQLCommands { get; set; }
        public Dictionary<string, SQLCmdProperty> UpdateSQLCommands { get; set; }
        public Dictionary<string, SQLCmdProperty> DeleteSQLCommands { get; set; }

        public SQLMapping()
        {
            SelectSQLCommands = new Dictionary<string, SQLCmdProperty>();
            InsertSQLCommands = new Dictionary<string, SQLCmdProperty>();
            UpdateSQLCommands = new Dictionary<string, SQLCmdProperty>();
            DeleteSQLCommands = new Dictionary<string, SQLCmdProperty>();
        }

        public bool Load(Object o)
        {
            if (!SelectSQLCommands.ContainsKey(""))
            {
                throw new Exception("Select Default SQLCmdProperty 不存在！");
            }
            SQLCmdProperty sqlCmdProperty = SelectSQLCommands[""];
            FieldMapping primaryfield = PrimaryKeyField;
            if (primaryfield == null)
            {
                throw new Exception("没有定义任何主键！");
            } 
            SqlCommand cmd = new SqlCommand();
            object value = primaryfield.GetValue(o);
            if (value == null)
            {
                throw new Exception("主键 " + primaryfield.FieldName + " 的值不能为null！");
            }
            else
            {
                string parameterName = "@" + primaryfield.FieldName;
                cmd.Parameters.AddWithValue(parameterName, value);
            }
            cmd.CommandText = sqlCmdProperty.SQLCommand;
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count == 1)
            {
                foreach (FieldMapping fieldMapping in DBFields)
                {
                    if (fieldMapping.IsMapping && (ds.Tables[0].Columns.Contains(fieldMapping.DBFieldName)))
                    {
                        Object fieldvalue = ds.Tables[0].Rows[0][fieldMapping.DBFieldName];
                        object returnValue = null;
                        if (!Convert.IsDBNull(fieldvalue))
                        {
                            returnValue = Convert.ChangeType(fieldvalue, fieldMapping.FieldType);
                        }
                        fieldMapping.SetValue(o, returnValue);
                    }
                }
                return true;
            }
            return false;
        }

        public bool Find(Object o)
        {
            return Find(o, string.Empty);
        }

        public bool Find(Object o, string selectCommandName)
        {
            if (!SelectSQLCommands.ContainsKey(selectCommandName))
            {
                throw new Exception("Select Default SQLCmdProperty 不存在！");
            }
            SQLCmdProperty sqlCmdProperty = SelectSQLCommands[selectCommandName];
            SqlCommand cmd = new SqlCommand();
            foreach (FieldMapping fieldMapping in DBFields)
            {
                object value = fieldMapping.GetValue(o);
                if (fieldMapping.IsMapping && (value != null))
                {
                    string parameterName = "@" + fieldMapping.FieldName;
                    cmd.Parameters.AddWithValue(parameterName, value);
                }
            }
            cmd.CommandText = sqlCmdProperty.SQLCommand;
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (FieldMapping fieldMapping in DBFields)
                {
                    if (fieldMapping.IsMapping && (ds.Tables[0].Columns.Contains(fieldMapping.DBFieldName)))
                    {
                        Object fieldvalue = ds.Tables[0].Rows[0][fieldMapping.DBFieldName];
                        object value = null;
                        if (!Convert.IsDBNull(fieldvalue))
                        {
                            value = Convert.ChangeType(fieldvalue, fieldMapping.FieldType);
                        }
                        fieldMapping.SetValue(o, value);
                    }
                }
                return true;
            }
            return false;
        }

        public IList<T> FindList<T>(Object o)
        {
            return FindList<T>(o, "");
        }

        public IList<T> FindList<T>(object o, string selectCommandName = "")
        {
            if(!SelectSQLCommands.ContainsKey(selectCommandName))
            {
                throw new Exception("Select SQLCmdProperty : \"" + selectCommandName.ToString() + "\" 不存在！");
            }
            SQLCmdProperty sqlCmdProperty = SelectSQLCommands[selectCommandName];
            return FindList<T>(o, sqlCmdProperty);
        }
        /*
        public IList<Object> FindList(this object o, int selectCommandIndex)
        {
            SelectSQLCommands
            SQLCmdProperty sqlCmdProperty = SelectSQLCommands.Keys[1]
            SelectAttribute[] atts = getSelectCommands(o);
            if (selectCommandIndex > atts.Length - 1)
            {
                throw new Exception("Select SQLCmdProperty [" + selectCommandIndex.ToString() + "] 超出范围！");
            }
            att = atts[atts.Length - selectCommandIndex - 1];
            return FindList(o, att);
        }
         * */

        private IList<T> FindList<T>(Object o, SQLCmdProperty sqlCmdProperty)
        {
            SortedBindingList<T> list = new SortedBindingList<T>();
            if (sqlCmdProperty == null)
            {
                throw new Exception("select SQLCmdProperty 为 null！");
            }
            SqlCommand cmd = new SqlCommand();
            foreach (FieldMapping fieldMapping in DBFields)
            {
                object value = fieldMapping.GetValue(o);
                if (fieldMapping.IsMapping && (value != null))
                {
                    string parameterName = "@" + fieldMapping.FieldName;
                    cmd.Parameters.AddWithValue(parameterName, value);
                }
            }
            cmd.CommandText = sqlCmdProperty.SQLCommand;
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                T newObject = (T)Activator.CreateInstance(o.GetType());
                foreach (FieldMapping fieldMapping in DBFields)
                {
                    if (fieldMapping.IsMapping && (ds.Tables[0].Columns.Contains(fieldMapping.DBFieldName)))
                    {
                        Object fieldvalue = ds.Tables[0].Rows[i][fieldMapping.DBFieldName];
                        object value = null;
                        if (!Convert.IsDBNull(fieldvalue))
                        {
                            value = Convert.ChangeType(fieldvalue, fieldMapping.FieldType);
                        }
                        fieldMapping.SetValue(newObject, value);
                    }
                }
                list.Add(newObject);
            }
            return list;
        }

        public bool Insert(Object o, string insertCommandName = "")
        {
            if (!InsertSQLCommands.ContainsKey(insertCommandName))
            {
                throw new Exception("Insert SQLCmdProperty " + insertCommandName + " 不存在！");
            }
            SQLCmdProperty sqlCmdProperty = InsertSQLCommands[insertCommandName];
            bool insertReturn = false;
            if (AutoGeneratePrimaryKeyField != null)
            {
                SqlCommand cmd = getSQLCommand(o, sqlCmdProperty);
                cmd.CommandText += "; SELECT SCOPE_IDENTITY() ";
                object keyvalue = cmd.ExecuteScalar();
                object identity = null;
                if (!Convert.IsDBNull(keyvalue))
                {
                    identity = Convert.ChangeType(keyvalue, AutoGeneratePrimaryKeyField.FieldType);
                    AutoGeneratePrimaryKeyField.SetValue(o, identity);
                    insertReturn = true;
                }
                else
                {
                    insertReturn = false;
                }
            }
            else
            {
                insertReturn = executeSQL(o, sqlCmdProperty);
            }
            if (insertReturn && sqlCmdProperty.ReloadAfterInsert)
            {
                o.Load();
            }
            return insertReturn;
        }

        public bool Update(Object o, string updateCommandName = "")
        {
            if (!UpdateSQLCommands.ContainsKey(updateCommandName))
            {
                throw new Exception("Update SQLCmdProperty " + updateCommandName + " 不存在！");
            }
            SQLCmdProperty sqlCmdProperty = UpdateSQLCommands[updateCommandName];
            bool updateReturn = executeSQL(o, sqlCmdProperty);
            if (updateReturn && sqlCmdProperty.ReloadAfterUpdate)
            {
                o.Load();
            }
            return updateReturn;
        }

        public bool Delete(Object o, string deleteCommandName = "")
        {
            if (!DeleteSQLCommands.ContainsKey(deleteCommandName))
            {
                throw new Exception("Delete SQLCmdProperty " + deleteCommandName + " 不存在！");
            }
            SQLCmdProperty sqlCmdProperty = DeleteSQLCommands[deleteCommandName];
            return executeSQL(o, sqlCmdProperty);
        }

        private SqlCommand getSQLCommand(Object o, SQLCmdProperty sqlCmdProperty)
        {
            SqlCommand cmd = new SqlCommand();
            foreach (FieldMapping fieldmapping in DBFields)
            {
                object value = fieldmapping.GetValue(o);
                if (fieldmapping.IsMapping && (value != null))
                {
                    string parameterName = "@" + fieldmapping.FieldName;
                    cmd.Parameters.AddWithValue(parameterName, value);
                }
            }
            cmd.CommandText = sqlCmdProperty.SQLCommand;
            cmd.Connection = DAOExtension.Connection;
            cmd.Transaction = DAOExtension.Transaction;
            return cmd;
        }

        private bool executeSQL(Object o, SQLCmdProperty sqlCmdProperty)
        {
            SqlCommand cmd = getSQLCommand(o, sqlCmdProperty);
            if (cmd.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }
    }
}